﻿using Newtonsoft.Json.Linq;
using System;
using System.Data;
using System.Data.Common;
using System.Text;

namespace CommonUtils
{
    /// <summary>
    /// ADO.Net Base
    /// </summary>
    public abstract partial class DbBase
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        protected string ConnectString { get; set; }

        /// <summary>
        /// 初始化
        /// </summary>
        protected DbBase(string connectString)
        => ConnectString = connectString;

        /// <summary>
        /// 获取连接器
        /// </summary>
        protected abstract DbConnection GetConnection();

        /// <summary>
        /// 连接测试
        /// </summary>
        public string GetConnectTestInfo()
        {
            var result = new StringBuilder();
            result.AppendLine(ConnectString);
            result.AppendLine("Db Connect Test...");
            var connection = GetConnection();
            try
            {
                connection.Open();
                connection.Close();
                result.AppendLine("Db Connect Success.");
            }
            catch (Exception ex)
            {
                result.AppendLine("Db Connect Fail:" + ex.Message);
            }
            finally
            {
                connection.Dispose();
            }
            Console.WriteLine(result);
            return result.ToString();
        }

        public string ConnectTest()
        => GetConnectTestInfo();

        /// <summary>
        /// 获取参数
        /// </summary>
        protected abstract DbParameter GetParameter(string key, object value);

        /// <summary>
        /// 获取参数
        /// </summary>
        protected DbParameter[] CreateParameters(StringObjectMap args)
        {
            var array = new DbParameter[args.Count];
            var index = 0;
            foreach (var keyValue in args)
                array[index++] = GetParameter(keyValue.Key, keyValue.Value);
            return array;
        }

        /// <summary>
        /// 获取Command
        /// </summary>
        private DbCommand CreateCommand(string sql, StringObjectMap args, out DbConnection connection)
        {
            connection = GetConnection();
            var cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            if (args != null)
                cmd.Parameters.AddRange(CreateParameters(args));
            sql.AppendNextLine("args:" + args.ToJson(true)).Print();
            return cmd;
        }

        /// <summary>
        /// 执行
        /// </summary>
        public int Execute(string sql, StringObjectMap args = null)
        {
            var cmd = CreateCommand(sql, args, out var connection);
            connection.Open();
            var count = cmd.ExecuteNonQuery();
            connection.Close();
            return count;
        }

        public int ExecuteFormat(string sql, params object[] args)
        => Execute(string.Format(sql, args));

        /// <summary>
        /// 获取执行行数
        /// </summary>
        public int GetExecuteNumber(string sql, StringObjectMap args = null)
        => Execute(sql, args);

        /// <summary>
        /// 判断影响行数是否大于0
        /// </summary>
        public bool GetExecuteResult(string sql, StringObjectMap args = null)
        => GetExecuteNumber(sql, args) > 0;

        /// <summary>
        /// 获取单个数据
        /// </summary>
        public object GetValue(string sql, StringObjectMap args)
        {
            var cmd = CreateCommand(sql, args, out var connection);
            connection.Open();
            var value = cmd.ExecuteScalar();
            connection.Close();
            return value;
        }

        /// <summary>
        /// 获取单个字符串
        /// </summary>
        public string GetString(string sql, StringObjectMap args = null)
        => GetValue(sql, args).ToString();

        /// <summary>
        /// 获取单个数字
        /// </summary>
        public int GetIntger(string sql, StringObjectMap args = null)
        => GetValue(sql, args).ToInt();

        /// <summary>
        /// 获取数据指针（需关闭）
        /// </summary>
        public DbDataReader GetDataReader(string sql, StringObjectMap args = null)
        {
            var cmd = CreateCommand(sql, args, out var connection);
            connection.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public T SelectValue<T>(string sql, StringObjectMap args = null)
        {
            var value = GetValue(sql, args);
            if (value.GetType() == typeof(DBNull))
                return default;
            return (T)value;
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        public DataTable GetDataTable(string sql, StringObjectMap args = null)
        => GetDataReader(sql, args).GetDataTable();

        /// <summary>
        /// 获取单列value集 基本数据类型
        /// </summary>
        public T[] GetValues<T>(string sql, StringObjectMap args = null)
        => GetDataReader(sql, args).GetValueArray<T>();

        /// <summary>
        /// 获取Newtonsoft.Json.Linq.JArray
        /// </summary>
        public JArray GetJArray(string sql, StringObjectMap args = null)
        => GetDataReader(sql, args).GetJArray();

        /// <summary>
        /// 获取结构数组 可json序列化的结构
        /// </summary>
        public T[] GetStructArray<T>(string sql, StringObjectMap args = null)
        => GetDataReader(sql, args).GetStructArray<T>();

        /// <summary>
        /// 获取数据器
        /// </summary>
        [Obsolete("在oracle中报错,在Sqlite中不存在")]
        protected abstract DbDataAdapter GetDataAdapter(DbCommand cmd);

        /// <summary>
        /// 获取数据集
        /// </summary>
        [Obsolete("不建议使用，因为用到了DbDataAdapter，DbDataAdapter在oracle中报错,在Sqlite中不存在")]
        public DataSet GetDataSet(string sql, StringObjectMap args = null)
        {
            var cmd = CreateCommand(sql, args, out _);
            //connection没开
            var adapter = GetDataAdapter(cmd);
            var set = new DataSet();
            adapter.Fill(set);
            adapter.Dispose();
            return set;
        }

        /// <summary>
        /// 批量插入(imp by sdk)
        /// </summary>
        public virtual void BulkInsert(DataTable table)
        => throw new NotImplementedException();

        /// <summary>
        /// 批量插入(imp by sql)
        /// </summary>
        public void BatchInsert(DataTable table)
        {
            ConsoleUtil.Info("to BatchInsert count: " + table.Rows.Count);
            if (table.Rows.Count == 0)
                return;

            //模板 insert into test values (1,'1','1'),(1,'1','2'),(1,'1','3');
            var sql = new StringBuilder(string.Format("insert into {0}{2}{1} values ", FieldStartIdentifier, FieldEndIdentifier, table.TableName));
            var args = new StringObjectMap();
            var columnCount = table.Columns.Count;
            var rowCount = table.Rows.Count;
            for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
            {
                if (rowIndex != 0)
                    sql.Append(',');
                sql.Append('(');
                for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
                {
                    if (columnIndex != 0)
                        sql.Append(',');
                    var name = string.Format("{0}r{1}c{2}", ParameterIdentifier, rowIndex, columnIndex);
                    sql.Append(name);
                    args.Add(name, table.Rows[rowIndex][columnIndex]);
                }
                sql.Append(')');
            }
            sql.Append(';');
            GetExecuteResult(sql.ToString(), args);
        }
    }
}
